package intprpt.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import db.dbConnection;
import intprpt.bean.Intprpt;

public class IntprptService {
  dbConnection db=new dbConnection();
  
  public List ShowAllIntprpt(int pagenow,int pagesize){
	  List<Intprpt> list=new ArrayList();
	  String sql="select * from intprpt limit ?,?";
	  Connection conn=db.getConnection();  
	  try {
			PreparedStatement past = conn.prepareStatement(sql);
			past.setInt(1, pagenow*pagesize);
			past.setInt(2, pagesize);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				Intprpt intprpt=new Intprpt();
				intprpt.setContent(rs.getString("content"));
				intprpt.setId(rs.getInt("id"));
				intprpt.setTitle(rs.getString("title"));
				intprpt.setImg(rs.getString("img"));
				list.add(intprpt);
			}		
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
	  return list;
  }
 
  public int CountAllIntprpt(){
	  int count=0;
	  String sqlString="select count(*) from intprpt ";
	 Connection conn=db.getConnection();
	 try{
		 PreparedStatement past=conn.prepareStatement(sqlString);
		 ResultSet rSet=past.executeQuery();
		 while(rSet.next()){
			 count=rSet.getInt(1);
		 }
	 }catch(Exception e){
		 e.printStackTrace();
	 }
	  return count; 	
  }
  public boolean AddIntprpt(Intprpt intprpt,String imgFileName){
	 String title=intprpt.getTitle();
	  String content=intprpt.getContent();
	  content=content.replace("\n","<br/>");
	  content=content.replace(" ", "&nbsp;&nbsp;");
	  content=content.replace("m³", "m&sup3;");
	  String img=imgFileName;

	  boolean flag = false;
	  Connection conn = db.getConnection();
		try {
			PreparedStatement past = null;
			String sql = "insert into intprpt(title,content,img) values ("
					+ "'" + title
					+ "'" + "," + "'" + content + "'" + ","+"'"+ img + "'"+")";
			past = conn.prepareStatement(sql);
			past.executeUpdate(sql);
			flag = true;
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
  }
  
  public Intprpt ShowoneIntprpt(Intprpt intprptbean){
	  Intprpt intprpt=new Intprpt();
	  String sql="select * from intprpt where id=?";
			  Connection conn=db.getConnection();
	  		  PreparedStatement past=null;
	  		try {
				past = conn.prepareStatement(sql);
				past.setInt(1, intprptbean.getId());
				ResultSet rs = past.executeQuery();
				if (!rs.next()) {
					return null;
				}
				intprpt.setId(rs.getInt("id"));
				intprpt.setContent(rs.getString("content"));
				intprpt.setTitle(rs.getString("title"));
				intprpt.setImg(rs.getString("img"));
				rs.close();
				past.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				db.closeConnection(conn);
			}
			return intprpt;
		}
	  public boolean DeleteIntprpt(Intprpt intprptbean){
		  boolean flag = false;
			Connection conn = db.getConnection();
			String sql1 = "delete from intprpt where id =" + "'"
					+ intprptbean.getId() + "'";
			
			try {
				PreparedStatement past = null;
				past = conn.prepareStatement(sql1);
				past.executeUpdate(sql1);
				flag = true;
				past.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				db.closeConnection(conn);
			}
			return flag;
	  }
	  public boolean ModifyIntprpt(Intprpt intprptbean){
		  boolean flag = false;
			String sql = "update intprpt set title=?,content=?,img=? where id=?";
			Connection conn = db.getConnection();
			String content=intprptbean.getContent();
			  content=content.replace("\n","<br/>");
			  content=content.replace(" ", "&nbsp;&nbsp;");
			  content=content.replace("m³", "m&sup3;");
			PreparedStatement past = null;
			try {
				past = conn.prepareStatement(sql);
				past.setString(1,intprptbean.getTitle());
				past.setString(2, content);
				past.setString(3, intprptbean.getImg());
				past.setInt(4, intprptbean.getId());

				past.executeUpdate();
				past.close();
				flag = true;
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				db.closeConnection(conn);
			}
			return flag;
	  }
	  
  }

